<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class Helpdesk_model extends CI_Model
{
    
    function __construct()
    {
       parent::__construct();
		$this->CI = get_instance();
    }//end of function

    function add_helpdesk($data){
        
        $this->db->insert('HD_TRANS',$data);
        
    }//end of function
    
    function edit_helpdesk($id_helpdesk,$data){
        
        $this->db->where('KODE',$id_helpdesk);
        $this->db->update('HD_TRANS',$data);        
        
    }//end of function

    function get_helpdesk(){
        
        $this->db->select('*');
        $this->db->from('HD_TRANS');
        //$this->db->where('STATUS > 0');
        $query = $this->db->get();
        return $query;        
        
    }//end of function
    
	function get_id_helpdesk($id_helpdesk){
		$this->db->select('*');
		$this->db->from('HD_TRANS');
		$this->db->where('KODE',$id_helpdesk);
		$query = $this->db->get();
		return $query;
	}	

	function get_helpdesk_tahun($tahun){
		$this->db->select('*');
		$this->db->from('HD_TRANS');
		$this->db->where("to_char(tgl_entry,'rrrr') = ".$tahun);
		$this->db->order_by('tgl_entry','ASC');
		$query = $this->db->get();
		return $query;
	}

	function get_helpdesk_bulan_tahun($bulan,$tahun){
		$this->db->select('*');
		$this->db->from('HD_TRANS');
		$this->db->where("to_char(tgl_entry,'mmrrrr') = ".$bulan.$tahun);
		$this->db->order_by('tgl_entry','ASC');
		$query = $this->db->get();
		return $query;
	}

	function get_all_divisi(){
		$this->db->select('PERSO.PS_101.KODE_UNIT_KERJA,NAMA_UNIT_KERJA');
		$this->db->from('PERSO.PS_101,PERSO.PS_201');
		$this->db->where("PERSO.PS_101.KODE_UNIT_KERJA = PERSO.PS_201.KODE_UNIT_KERJA");
		$this->db->distinct();
		$query = $this->db->get();
		return $query->result();
	}

	function get_divisi_user($user){
		$sql = "SELECT distinct nama_unit_kerja FROM hd_trans a, PERSO.PS_101 b , PERSO.PS_201 c
		WHERE a.user_entry LIKE ? AND a.user_entry = substr(b.nomor_pekerja,2) AND b.kode_unit_kerja = c.kode_unit_kerja"; 
		return $this->db->query($sql, $user)->result();
	}

	function get_helpdesk_divisi_tahun($divisi,$tahun){
		$sql = "SELECT distinct KODE, JENIS, SUB_JENIS, ISI, TGL_ENTRY, USER_ENTRY, URAIAN, USER_KERJA, a.KETERANGAN, TGL_SELESAI, 
		KET_SELESAI from hd_trans a, PERSO.PS_101 b , PERSO.PS_201 c
		WHERE b.kode_unit_kerja LIKE ? and a.user_entry = substr(b.nomor_pekerja,2) and b.kode_unit_kerja = c.kode_unit_kerja and to_char(tgl_entry,'rrrr') = ?";
		return $this->db->query($sql, array($divisi,$tahun));
	}

	function get_helpdesk_divisi_bulan_tahun($divisi,$bulantahun){
		$sql = "SELECT distinct KODE, JENIS, SUB_JENIS, ISI, TGL_ENTRY, USER_ENTRY, URAIAN, USER_KERJA, a.KETERANGAN, TGL_SELESAI, 
		KET_SELESAI from hd_trans a, PERSO.PS_101 b , PERSO.PS_201 c
		WHERE b.kode_unit_kerja LIKE ? and a.user_entry = substr(b.nomor_pekerja,2) and b.kode_unit_kerja = c.kode_unit_kerja and to_char(tgl_entry,'mmrrrr') = ?";
		return $this->db->query($sql, array($divisi,$bulantahun));
	}
	
	function get_helpdesk_grid(){	
		$this->db->select('*');
		$this->db->from('HD_TRANS'); 
		$this->CI->flexigrid->build_query();
		
		$return['records'] = $this->db->get();
		
		$this->db->select('*');
		$this->db->from('HD_TRANS');
		$this->CI->flexigrid->build_query(FALSE);
		
		$return['record_count'] = $this->db->count_all_results();

		return $return;
	}

	function get_helpdesk_personal_grid($username){	
		$this->db->select('*');
		$this->db->from('HD_TRANS');
		$this->db->where('USER_KERJA',$username); 
		$this->CI->flexigrid->build_query();
		
		$return['records'] = $this->db->get();
		
		$this->db->select('*');
		$this->db->from('HD_TRANS');
		$this->db->where('USER_KERJA',$username);
		$this->CI->flexigrid->build_query(FALSE);
		
		$return['record_count'] = $this->db->count_all_results();

		return $return;
	}

	function get_nama_entry($nip){
		$this->db->select('NAMA_PEKERJA');
		$this->db->from('HELPDESK.V_ALL_PEG');
		$this->db->where("substr(NOMOR_PEKERJA,2) = ".$nip);
		$query = $this->db->get();
		return $query->result();
	}

	function get_nama_kerja($nip){
		$this->db->select('NAMA');
		$this->db->from('HD_PEGAWAI');
		$this->db->where('KODE',$nip);
		$query = $this->db->get();
		return $query->result();
	}

	function get_all_nama_kerja(){
		$this->db->select('*');
		$this->db->from('HD_PEGAWAI');
		$query = $this->db->get();
		return $query->result();
	}

	function count_all_helpdesk(){
		$this->db->select('*');
		$this->db->from('HD_TRANS');
		$query = $this->db->get();
		return $query->num_rows();
	}

	function count_requested_helpdesk($bulan,$tahun){
		$sql = "SELECT count(KODE) as JUMLAH FROM hd_trans WHERE to_char(tgl_entry,'mm') = ? and to_char(tgl_entry,'rrrr') = ?"; 
		return $this->db->query($sql, array($bulan,$tahun));
	}

	function count_solved_helpdesk($bulan,$tahun){
		$sql = "SELECT count(KODE) as JUMLAH FROM hd_trans WHERE to_char(tgl_selesai,'mm') = ? and to_char(tgl_selesai,'rrrr') = ?"; 
		return $this->db->query($sql, array($bulan,$tahun));
	}
		
}//end of class